"""
设备事件和操作日志 CRUD
与 device_crud.py 风格 100% 对齐

职责划分:
- 设备事件: 记录设备生命周期中的重大事件(替换/报废/故障/恢复等)
- 操作日志: 记录设备的日常CRUD操作(创建/更新/删除等)
"""
from typing import List, Optional, Tuple

from app.models.device_models import DeviceEvent, DeviceOperationLog
from app.schemas.device_schemas import DeviceEventQuery, DeviceOperationLogQuery
from common.database import DatabaseService
from common.exceptions import BizException, ErrorCode
from common.logger import create_logger

logger = create_logger("device_event_crud")


# ---------- 设备事件 ----------


async def get_device_event_by_id(db: DatabaseService, event_id: int) -> Optional[DeviceEvent]:
	sql = """
        SELECT id, target_type, target_id, target_serial_no, event_type, event_time, operator_id,
               related_device_id, old_value, new_value, reason, photo_files,
               work_order_id, details, created_at
        FROM dev_lifecycle_event
        WHERE id = %s
    """
	try:
		row = db.get_one(sql, (event_id,))
		if not row:
			return None
		row["id"] = int(row["id"])
		row["target_id"] = int(row["target_id"])
		row["operator_id"] = int(row["operator_id"])
		return DeviceEvent(**row)
	except Exception as e:
		logger.exception("查询设备事件失败，event_id=%s", event_id)
		raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR, message="查询设备事件失败") from e


async def get_device_event_list(db: DatabaseService, query: DeviceEventQuery) -> Tuple[int, List[DeviceEvent]]:
	where_conditions: List[str] = []
	params: List = []

	if query.keyword:
		where_conditions.append("(e.reason LIKE %s OR e.details LIKE %s)")
		params.extend([f"%{query.keyword}%", f"%{query.keyword}%"])

	# 新增: 按设备ID过滤
	if query.device_id:
		where_conditions.append("e.target_id = %s")
		params.append(query.device_id)

	# 新增: 按设备类型过滤
	if query.device_type:
		where_conditions.append("e.target_type = %s")
		params.append(query.device_type)

	# 新增: 按事件类型过滤
	if query.event_type:
		where_conditions.append("e.event_type = %s")
		params.append(query.event_type)

	# 新增: 时间范围过滤
	if query.start_time:
		where_conditions.append("e.event_time >= %s")
		params.append(query.start_time)
	if query.end_time:
		where_conditions.append("e.event_time <= %s")
		params.append(query.end_time)

	where_clause = " AND ".join(where_conditions)
	if where_clause:
		where_clause = f"WHERE {where_clause}"

	count_sql = f"SELECT COUNT(*) AS count FROM dev_lifecycle_event e {where_clause}"
	try:
		count_row = db.get_one(count_sql, params)
		total = count_row["count"] if count_row else 0
		if total == 0:
			return 0, []

		offset = (query.page - 1) * query.size
		select_sql = f"""
            SELECT
			  e.id,e.target_type,e.target_id, e.target_serial_no, e.event_type,e.event_time, e.operator_id,
			  e.related_device_id, e.old_value, e.new_value,e.reason,e.photo_files, e.work_order_id,e.created_at,
			  u.NAME AS user_name,
			  CASE
				e.target_type
				WHEN 'STATION' THEN
				  ds.CODE
				WHEN 'BOX' THEN
				  db.CODE
				WHEN 'RTU' THEN
				  dr.CODE
				WHEN 'METER' THEN
				  dm.CODE
			  END AS device_code
			FROM
			  dev_lifecycle_event e
			  LEFT JOIN base_user u ON u.id = e.operator_id
			  LEFT JOIN dev_station ds ON e.target_type = 'STATION' AND ds.id = e.target_id
			  LEFT JOIN dev_box db ON e.target_type = 'BOX' AND db.id = e.target_id
			  LEFT JOIN dev_rtu dr ON e.target_type = 'RTU' AND dr.id = e.target_id
			  LEFT JOIN dev_meter dm ON e.target_type = 'METER' AND dm.id = e.target_id
            {where_clause}
            ORDER BY e.event_time DESC
            LIMIT %s OFFSET %s
        """
		params.extend([query.size, offset])
		rows = db.get_all(select_sql, params)

		events = []
		for row in rows:
			events.append(DeviceEvent(**row))
		return total, events
	except Exception as e:
		logger.exception("分页查询设备事件失败，query=%s", query)
		raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR, message="查询设备事件列表失败") from e


async def create_device_event(db: DatabaseService, payload: dict) -> int:
	"""
	创建设备生命周期事件
	"""
	sql = """
        INSERT INTO dev_lifecycle_event
        (target_type, target_id, target_serial_no, event_type, event_time, operator_id,
         related_device_id, old_value, new_value, reason, photo_files,
         work_order_id, details)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    """
	try:
		params = (
			payload.get("target_type"),
			payload.get("target_id"),
			payload.get("target_serial_no"),
			payload.get("event_type"),
			payload.get("event_time"),
			payload.get("operator_id"),
			payload.get("related_device_id"),
			payload.get("old_value"),
			payload.get("new_value"),
			payload.get("reason"),
			payload.get("photo_files"),
			payload.get("work_order_id"),
			payload.get("details"),
		)
		return db.insert(sql, params)
	except Exception as e:
		logger.exception("创建设备事件失败，payload=%s", payload)
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message="创建设备事件失败") from e


# ---------- 操作日志 ----------


async def get_device_operation_log_by_id(db: DatabaseService, log_id: int) -> Optional[DeviceOperationLog]:
	sql = """
        SELECT id, target_type, target_id, op_type, op_by, op_at, note, old_vals, new_vals
        FROM dev_op_log
        WHERE id = %s
    """
	try:
		row = db.get_one(sql, (log_id,))
		if not row:
			return None
		row["id"] = int(row["id"])
		row["target_id"] = int(row["target_id"])
		row["op_by"] = int(row["op_by"])
		return DeviceOperationLog(**row)
	except Exception as e:
		logger.exception("查询设备操作日志失败，log_id=%s", log_id)
		raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR, message="查询设备操作日志失败") from e


async def get_device_operation_log_list(
		db: DatabaseService, query: DeviceOperationLogQuery
) -> Tuple[int, List[DeviceOperationLog]]:
	where_conditions: List[str] = []
	params: List = []

	if query.keyword:
		where_conditions.append("(note LIKE %s OR op_type LIKE %s)")
		params.extend([f"%{query.keyword}%", f"%{query.keyword}%"])

	#  按设备ID过滤
	if query.device_id:
		where_conditions.append("target_id = %s")
		params.append(query.device_id)

	# 按设备类型过滤
	if query.device_type:
		where_conditions.append("target_type = %s")
		params.append(query.device_type)

	#  按操作类型过滤
	if query.op_type:
		where_conditions.append("op_type = %s")
		params.append(query.op_type)

	#  时间范围过滤
	if query.start_time:
		where_conditions.append("op_at >= %s")
		params.append(query.start_time)
	if query.end_time:
		where_conditions.append("op_at <= %s")
		params.append(query.end_time)

	where_clause = " AND ".join(where_conditions)
	if where_clause:
		where_clause = f"WHERE {where_clause}"

	count_sql = f"SELECT COUNT(*) AS count FROM dev_op_log {where_clause}"
	try:
		count_row = db.get_one(count_sql, params)
		total = count_row["count"] if count_row else 0
		if total == 0:
			return 0, []

		offset = (query.page - 1) * query.size
		select_sql = f"""
            SELECT id, target_type, target_id, op_type, op_by, op_at, note, old_vals, new_vals
            FROM dev_op_log
            {where_clause}
            ORDER BY op_at DESC
            LIMIT %s OFFSET %s
        """
		params.extend([query.size, offset])
		rows = db.get_all(select_sql, params)

		logs = []
		for row in rows:
			row["id"] = int(row["id"])
			row["target_id"] = int(row["target_id"])
			row["op_by"] = int(row["op_by"])
			logs.append(DeviceOperationLog(**row))
		return total, logs
	except Exception as e:
		logger.exception("分页查询设备操作日志失败，query=%s", query)
		raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR, message="查询设备操作日志列表失败") from e


async def create_device_operation_log(db: DatabaseService, payload: dict) -> int:
	sql = """
        INSERT INTO dev_op_log
        (target_type, target_id, op_type, op_by, op_at, note, old_vals, new_vals)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
    """
	try:
		params = (
			payload.get("target_type"),
			payload.get("target_id"),
			payload.get("op_type"),
			payload.get("op_by"),
			payload.get("op_at"),
			payload.get("note"),
			payload.get("old_vals"),
			payload.get("new_vals"),
		)
		return db.insert(sql, params)
	except Exception as e:
		logger.exception("创建设备操作日志失败，payload=%s", payload)
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message="创建设备操作日志失败") from e


# ---------- 按设备查询便捷方法 ----------


async def get_device_events_by_device(
		db: DatabaseService,
		device_id: int,
		device_type: str,
		page: int = 1,
		size: int = 20
) -> Tuple[int, List[DeviceEvent]]:
	"""按设备ID和类型查询设备事件"""
	query = DeviceEventQuery(
		page=page,
		size=size,
		device_id=device_id,
		device_type=device_type
	)
	return await get_device_event_list(db, query)


async def get_device_operation_logs_by_device(
		db: DatabaseService,
		device_id: int,
		device_type: str,
		page: int = 1,
		size: int = 20
) -> Tuple[int, List[DeviceOperationLog]]:
	"""按设备ID和类型查询设备操作日志"""
	query = DeviceOperationLogQuery(
		page=page,
		size=size,
		device_id=device_id,
		device_type=device_type
	)
	return await get_device_operation_log_list(db, query)


async def get_meter_rtu_history_events(
		db: DatabaseService,
		meter_serial_no: str,
		page: int = 1,
		size: int = 20
) -> Tuple[int, List[DeviceEvent]]:
	"""查询某电表的RTU历史事件(绑定/解绑/替换等)
	
	通过target_serial_no字段模糊匹配与该电表相关的RTU事件。
	"""
	# 构造查询条件 - 使用模糊匹配
	# 事件的target_type为METER,target_serial_no为电表序列号
	where_conditions = [
		"e.target_type = 'METER'",
		"e.event_type IN ('BOUND', 'UNBOUND', 'REPLACED', 'DECOMMISSIONED')",
		"e.target_serial_no LIKE %s"
	]
	# 使用模糊匹配，支持部分序列号查询
	params = [f'%{meter_serial_no}%']

	where_clause = " AND ".join(where_conditions)

	try:
		# 查询总数
		count_sql = f"SELECT COUNT(*) AS count FROM dev_lifecycle_event e WHERE {where_clause}"
		count_row = db.get_one(count_sql, tuple(params))
		total = count_row["count"] if count_row else 0

		if total == 0:
			return 0, []

		# 分页查询
		offset = (page - 1) * size
		select_sql = f"""
			SELECT
				e.id, e.target_type, e.target_id, e.target_serial_no, e.event_type, e.event_time, e.operator_id,
				e.related_device_id, e.old_value, e.new_value, e.reason, e.photo_files,
				e.work_order_id, e.details, e.created_at,
				u.NAME AS user_name,
				dm.CODE AS device_code
			FROM dev_lifecycle_event e
			LEFT JOIN base_user u ON u.id = e.operator_id
			LEFT JOIN dev_meter dm ON e.target_type = 'METER' AND dm.id = e.target_id
			WHERE {where_clause}
			ORDER BY e.event_time DESC
			LIMIT %s OFFSET %s
		"""
		params.extend([size, offset])
		rows = db.get_all(select_sql, tuple(params))

		events = [DeviceEvent(**row) for row in rows]
		return total, events
	except Exception as e:
		logger.exception("查询电表RTU历史事件失败，meter_serial_no=%s", meter_serial_no)
		raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR, message="查询电表RTU历史事件失败") from e
